package com.freezers.androidlogin.library;
import java.util.HashMap;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.database.DatabaseUtils.InsertHelper;
import android.util.Log;

//import android.util.Log;

@SuppressWarnings("deprecation")
public class DatabaseHandler extends SQLiteOpenHelper {

	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "android_products.db";

	// Login table name
	private static final String TABLE_LOGIN = "login";

	// Login Table Columns names
	private static final String KEY_ID = "id";
	private static final String KEY_NAME = "name";
	private static final String KEY_EMAIL = "email";
	private static final String KEY_UID = "uid";
	private static final String KEY_CREATED_AT = "created_at";
	
	
	// Products table name
	private static final String TABLE_PRODUCTS = "products";
	//Product Table Column names
//	private static final String TAG_ID = "";
	private static final String TAG_PID = "pid";
	private static final String TAG_PRODUCT_NAME = "name";
	private static final String TAG_PRODUCT_PRICE = "price";
	private static final String TAG_PRODUCT_DESCRIPTION = "description";
	private static final String TAG_CREATED_AT = "created_at";

	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {
		
		String CREATE_LOGIN_TABLE = "CREATE TABLE " + TABLE_LOGIN + "("
				+ KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," 
				+ KEY_NAME + " TEXT,"
				+ KEY_EMAIL + " TEXT UNIQUE,"
				+ KEY_UID + " TEXT,"
				+ KEY_CREATED_AT + " TEXT" + ")";
		db.execSQL(CREATE_LOGIN_TABLE);
		//
		//String DROP_PRODUCTS_TABLE = "DROP TABLE IF EXISTS " + TABLE_PRODUCTS;
		//String DROP_PRODUCTS_TABLE = "DROP TABLE IF EXISTS " + TABLE_PRODUCTS;
		//db.execSQL(DROP_PRODUCTS_TABLE);
		//db.delete(TABLE_PRODUCTS, null, null);
		
		String CREATE_PRODUCTS_TABLE = "CREATE TABLE " + TABLE_PRODUCTS + "("
			    + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," 
				+ TAG_PID + " INT,"
				+ TAG_PRODUCT_NAME + " TEXT,"
				+ TAG_PRODUCT_PRICE + " DECIMAL(10,3),"
				+ TAG_PRODUCT_DESCRIPTION + " TEXT,"
				+ TAG_CREATED_AT + " TEXT" + ")";
				//db.execSQL(DROP_PRODUCTS_TABLE);
			   db.execSQL(CREATE_PRODUCTS_TABLE);
		}
	
	// Creating Tables
	

	// Upgrading database
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		//db.delete(TABLE_PRODUCTS, null, null);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_LOGIN);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRODUCTS);
		// Create tables again
		onCreate(db);
		
	}

	/**
	 * Storing user details in database
	 * */
	public void addUser(String name, String email, String uid, String created_at) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_NAME, name); // Name
		values.put(KEY_EMAIL, email); // Email
		values.put(KEY_UID, uid); // Email
		values.put(KEY_CREATED_AT, created_at); // Created At

		// Inserting Row
		db.insert(TABLE_LOGIN, null, values);
		db.close(); // Closing database connection
	}
	
	/**
	 * Storing user details in database
	 *
	 * */
	

	public void addProducts(String pid, String name, String price, String description, String created_at) {
		
		//Log.e("Msg","hujamoto Start");
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("PRAGMA synchronous=OFF");
	    db.setLockingEnabled(false);
	    db.beginTransaction();
	    
		//Log.e("Msg","hujamoto Start");
		
		// Inserting Row
		
		//db.insert(TABLE_PRODUCTS, null, values);
		//db.insert(TABLE_PRODUCTS, KEY_ID, values);
		try{
			//Log.e("Msg","hujamoto Start");	
			ContentValues values = new ContentValues();
		
			values.put(TAG_PID, pid); // Product PID
			values.put(TAG_PRODUCT_NAME, name); // Product name
			values.put(TAG_PRODUCT_PRICE, price); // Product price
			values.put(TAG_PRODUCT_DESCRIPTION, description); // Product description
			values.put(TAG_CREATED_AT, created_at); // Product description
			
			
		String sql = "INSERT INTO "+ TABLE_PRODUCTS +" (pid, name, price, description, created_at) values(?,?,?,?,?)";
        SQLiteStatement insert = db.compileStatement(sql);
        
        insert.bindString(1, values.getAsString(TAG_PID));
        insert.bindString(2, values.getAsString(TAG_PRODUCT_NAME));
        insert.bindString(3, values.getAsString(TAG_PRODUCT_PRICE));
        insert.bindString(4, values.getAsString(TAG_PRODUCT_DESCRIPTION));
        insert.bindString(5, values.getAsString(TAG_CREATED_AT));
        insert.execute();
        //Log.e("Msg","hujamoto End");
		//db.insertWithOnConflict(TABLE_PRODUCTS, null, values, SQLiteDatabase.CONFLICT_IGNORE);
				   // db.close();
		
        db.setTransactionSuccessful();
	}
    catch (Exception e) {
        Log.w("SQLITE:",e );
    }
    finally {
    	
    	    
    }  
		 db.endTransaction();
 	    db.setLockingEnabled(true);
 	    db.execSQL("PRAGMA synchronous=NORMAL");
 	    db.close();
 	   //Log.e("Msg","Sqlite insert End");		
	}
	public void addProducts2(String pid, String name, String price, String description, String created_at) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.setLockingEnabled(false);
		db.beginTransaction();
		try{
			Log.e("Msg","hujamoto Start");	
		//SQLiteDatabase db = this.getWritableDatabase();
		
		ContentValues values = new ContentValues();
	
		values.put(TAG_PID, pid); // Product PID
		values.put(TAG_PRODUCT_NAME, name); // Product name
		values.put(TAG_PRODUCT_PRICE, price); // Product price
		values.put(TAG_PRODUCT_DESCRIPTION, description); // Product description
		values.put(TAG_CREATED_AT, created_at); // Product description
		
		db.insert(TABLE_PRODUCTS, null, values);
		
	}
    catch (Exception e) {
        Log.w("XML:",e );
    }
    finally {
    	 db.endTransaction();
    	    db.setLockingEnabled(true);
    	    db.execSQL("PRAGMA synchronous=NORMAL");
    	    db.close();
    }  
		
		// Inserting Row
		/*
		//db.insert(TABLE_PRODUCTS, null, values);
		//db.insert(TABLE_PRODUCTS, KEY_ID, values);
		db.insertWithOnConflict(TABLE_PRODUCTS, null, values, SQLiteDatabase.CONFLICT_IGNORE);
		*/		   // db.close();
		
	}

	public void addProducts3(String pid, String name, String price, String description, String created_at) {
		
		/*
		SQLiteDatabase db = this.getWritableDatabase();
		
		ContentValues values = new ContentValues();
	
		values.put(TAG_PID, pid); // Product PID
		values.put(TAG_PRODUCT_NAME, name); // Product name
		values.put(TAG_PRODUCT_PRICE, price); // Product price
		values.put(TAG_PRODUCT_DESCRIPTION, description); // Product description
		values.put(TAG_CREATED_AT, created_at); // Product description
		*/
		// Inserting Row
		/*
		//db.insert(TABLE_PRODUCTS, null, values);
		//db.insert(TABLE_PRODUCTS, KEY_ID, values);
		db.insertWithOnConflict(TABLE_PRODUCTS, null, values, SQLiteDatabase.CONFLICT_IGNORE);
		*/		   // db.close();
		
		SQLiteDatabase db = this.getWritableDatabase();
		db.beginTransaction();
		
	
		
		ContentValues values = new ContentValues();
		String sql = "INSERT INTO "+  TABLE_PRODUCTS +" (pid, name, price, description, created_at) VALUES (?, ?, ?, ?, ?)";
	/*
		values.put(TAG_PID, pid); // Product PID
		values.put(TAG_PRODUCT_NAME, name); // Product name
		values.put(TAG_PRODUCT_PRICE, price); // Product price
		values.put(TAG_PRODUCT_DESCRIPTION, description); // Product description
		values.put(TAG_CREATED_AT, created_at); // Product description
		 */
		SQLiteStatement stmt = db.compileStatement(sql);
		for (int i = 0; i < values.size(); i++) {
		   /*
			stmt.bindString(1, TAG_PID);
		    stmt.bindString(2, TAG_PRODUCT_NAME);
		    stmt.bindString(3, TAG_PRODUCT_PRICE);
		    stmt.bindString(4, TAG_PRODUCT_DESCRIPTION);
		    stmt.bindString(5, TAG_CREATED_AT);
		    */
			   stmt.bindString(1, values.getAsString(TAG_PID));
			    stmt.bindString(2, values.getAsString(TAG_PRODUCT_NAME));
			    stmt.bindString(3, values.getAsString(TAG_PRODUCT_PRICE));
			    stmt.bindString(4, values.getAsString(TAG_PRODUCT_DESCRIPTION));
			    stmt.bindString(5, values.getAsString(TAG_CREATED_AT));
		    stmt.execute();
		    stmt.clearBindings();
		}
		 
		db.setTransactionSuccessful();
		db.endTransaction();
					
	}
	
	/**
	 * Getting user data from database
	 * */
	public HashMap<String, String> getUserDetails(){
		HashMap<String,String> user = new HashMap<String,String>();
		String selectQuery = "SELECT  * FROM " + TABLE_LOGIN;
		 
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // Move to first row
        cursor.moveToFirst();
        if(cursor.getCount() > 0){
        	user.put("name", cursor.getString(1));
        	user.put("email", cursor.getString(2));
        	user.put("uid", cursor.getString(3));
        	user.put("created_at", cursor.getString(4));
        }
        cursor.close();
        db.close();
		// return user
		return user;
	}

	/**
	 * Getting user login status
	 * return true if rows are there in table
	 * */
	public int getRowCount() {
		String countQuery = "SELECT  * FROM " + TABLE_LOGIN;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		int rowCount = cursor.getCount();
		db.close();
		cursor.close();
		
		// return row count
		return rowCount;
	}
	
	public int getRowCountProducts() {
		String countQuery = "SELECT  * FROM " + TABLE_PRODUCTS;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		int rowCountProducts = cursor.getCount();
		db.close();
		cursor.close();
		
		// return row count
		return rowCountProducts;
	}
	
	/**
	 * Re crate database
	 * Delete all tables and create them again
	 * */
	public void resetTables(){
		SQLiteDatabase db = this.getWritableDatabase();
		// Delete All Rows
		db.delete(TABLE_LOGIN, null, null);
		db.close();
	}

	public void truncateProducts() {
		// TODO Auto-generated method stub
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRODUCTS);
		
		
		String CREATE_PRODUCTS_TABLE = "CREATE TABLE " + TABLE_PRODUCTS + "("
				+KEY_ID+ " INTEGER PRIMARY KEY AUTOINCREMENT," 
				+ TAG_PID + " INT,"
				+ TAG_PRODUCT_NAME + " TEXT,"
				+ TAG_PRODUCT_PRICE + " DECIMAL(10,3),"
				+ TAG_PRODUCT_DESCRIPTION + " TEXT,"
				+ TAG_CREATED_AT + " TEXT" + ")";
				//db.execSQL(DROP_PRODUCTS_TABLE);
			   db.execSQL(CREATE_PRODUCTS_TABLE);
		
	}

	
}
